﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Project_KhuyenMaiVang.DataProvider;
using Project_KhuyenMaiVang.Models;
using System.Data.SqlClient;
using System.Data;

namespace Project_KhuyenMaiVang.DAO
{
    public class MemberDAO : MyDataProvider
    {

        public List<MemberDTO> GetAllMember()
        {
            List<MemberDTO> result = new List<MemberDTO>();
            try
            {
                string QueryString = @"select mb.IDAccount, mb.TenKhachHang, mb.DiaChi, mb.Email, mb.TaiKhoanPayPal from Member mb, Account ac where ac.IDAccount = mb.IDAccount and ac.TinhTrang = 'True' and 'True' = 'True'";
                _Adapter = new SqlDataAdapter(QueryString, _Connection);
                DataSet dataset = new DataSet();
                _Adapter.Fill(dataset);
                //ArrayList arr = ConvertDataSetToArrayList(dataset);
                DataTable dt = dataset.Tables[0];

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    object ob = GetDataFromDataRow(dt, i);
                    result.Add((MemberDTO)ob);
                }

            }
            catch (System.Exception ex)
            {
                return null;
            }
            return result;
        }


        /// <summary>
        /// Get member from IDAccount
        /// </summary>
        /// <param name="IDAccount"></param>
        /// <returns></returns>
        public MemberDTO GetInfor(int IDAccount)
        {
            MemberDTO memberDTO = new MemberDTO();
            try
            {
                string strCommand = "select mb.IDAccount, mb.TenKhachHang, mb.DiaChi, mb.Email, mb.TaiKhoanPayPal from Member mb, Account ac"
                + " where ac.IDAccount = mb.IDAccount and ac.TinhTrang = 'True' and mb.IDAccount = " + IDAccount.ToString();
                _Adapter = new SqlDataAdapter(strCommand, _Connection);
                DataSet dataset = new DataSet();
                _Adapter.Fill(dataset);
                DataTable dt = dataset.Tables[0];
                object ob = GetDataFromDataRow(dt, 0);
                memberDTO = (MemberDTO)ob;
            }
            catch (Exception ex)
            {
                memberDTO = null;
            }

            return memberDTO;
        }

        /// <summary>
        /// Delete member
        /// </summary>
        /// <param name="IDAccount">int</param>
        /// <returns>bool</returns>
        public bool DeleteMember(int IDAccount)
        {
            try
            {
                string query = "update Account set TinhTrang = 'False' where TinhTrang = 'True' and IDAccount = " + IDAccount.ToString();
                this.executeNonQuery(query);
                return true;
            }
            catch (System.Exception ex)
            {
                ex.GetBaseException();
                return false;
            }
        }


        /// <summary>
        /// Update member
        /// </summary>
        /// <param name="input">MemberDTO</param>
        /// <returns>bool</returns>
        public bool UpdateMember(MemberDTO input)
        {
            try
            {
                string queryString = "update Member "
                + "set TenKhachHang = N'" + input.TenKhachHang.ToString() + "', "
                + "DiaChi = N'" + input.DiaChi.ToString() + "', "
                + "TaiKhoanPayPal = N'" + input.TaiKhoanPayPal.ToString() + "' "
                + "where IDAccount = " + input.IDAccount.ToString()
                + " and 'True' = (select TinhTrang from Account where IDAccount = " + input.IDAccount.ToString() + ")";
                this.executeNonQuery(queryString);
                return true;
            }
            catch (System.Exception ex)
            {
                ex.GetBaseException();
                return false;
            }
        }

        /// <summary>
        /// Insert member
        /// </summary>
        /// <param name="input">MemberDTO</param>
        /// <returns>bool</returns>
        public bool InsertMember(MemberDTO input)
        {
            try
            {
                string strSql = "insert into Member (IDAccount, TenKhachHang, DiaChi, Email, TaiKhoanPayPal) VALUES (@IDAccount, @TenKhachHang, @DiaChi, @Email, @TaiKhoanPayPal)";
                _Command = new SqlCommand(strSql, _Connection);
                SqlParameter[] param = new SqlParameter[5];

                param[0] = new SqlParameter("@IDAccount", SqlDbType.Int);
                param[1] = new SqlParameter("@TenKhachHang", SqlDbType.NVarChar, 50);
                param[2] = new SqlParameter("@DiaChi", SqlDbType.NVarChar, 150);
                param[3] = new SqlParameter("@Email", SqlDbType.NVarChar, 50);
                param[4] = new SqlParameter("@TaiKhoanPayPal", SqlDbType.NVarChar);

                param[0].Value = input.IDAccount;
                param[1].Value = input.TenKhachHang;
                param[2].Value = input.DiaChi;
                param[3].Value = input.Email;
                param[4].Value = input.TaiKhoanPayPal;

                for (int i = 0; i < param.Length; i++)
                {
                    _Command.Parameters.Add(param[i]);
                }

                _Command.CommandType = CommandType.Text;
                _Command.ExecuteNonQuery();
                return true;
            }
            catch (System.Exception ex)
            {
                ex.GetBaseException();
                return false;
            }
        }

        protected override object GetDataFromDataRow(System.Data.DataTable dt, int i)
        {
            MemberDTO memberDTO = new MemberDTO();

            memberDTO.IDAccount = Int32.Parse(dt.Rows[i]["IDAccount"].ToString());
            memberDTO.TenKhachHang = dt.Rows[i]["TenKhachHang"].ToString();
            memberDTO.DiaChi = dt.Rows[i]["DiaChi"].ToString();
            memberDTO.Email = dt.Rows[i]["Email"].ToString();
            memberDTO.TaiKhoanPayPal = dt.Rows[i]["TaiKhoanPayPal"].ToString();

            return (object)memberDTO;
        }
    }
}